#!/usr/bin/env python3
#
# Database generator for weathercomp.
# Removes all data - if any - and sets up fresh tables.

import sqlite3

# Dates are stored as TEXT in ISO8601 format as that isn't defined as caring
# about timezones, which is perfect as all dates in weathercomp are supposed
# to be local time. SQLite3 supports this.
query = """
DROP TABLE IF EXISTS Report;
DROP TABLE IF EXISTS Forecast;
DROP TABLE IF EXISTS Location;

CREATE TABLE Location (
    LocID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    Name TEXT NOT NULL, 
    Latitude REAL NOT NULL, 
    Longitude REAL NOT NULL
    );
CREATE TABLE Forecast (
    LocID INTEGER NOT NULL REFERENCES Location(LocID), 
    Source TEXT NOT NULL, 
    ObservationDate TEXT NOT NULL, 
    ForecastDate TEXT NOT NULL, 
    TempMin INTEGER NOT NULL, 
    TempMax INTEGER NOT NULL,
    PRIMARY KEY (LocID, Source, ObservationDate, ForecastDate) 
    ON CONFLICT IGNORE
    );
CREATE TABLE Report (
    Date TEXT NOT NULL, 
    LocID INTEGER NOT NULL REFERENCES Location(LocID),
    TempMin INTEGER NOT NULL,
    TempMax INTEGER NOT NULL,
    PRIMARY KEY (Date, LocID) ON CONFLICT IGNORE
    );
"""

con = sqlite3.connect('weathercomp.db')
con.executescript(query)
con.commit()
con.close()
